package com.github.McPlugin.dao;

import com.github.McPlugin.main;
import com.github.McPlugin.util.Task.Task;
import com.google.gson.Gson;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class MySQLManager {
    public static MySQLManager instance = null;
    private static Connection connection = null;

    public static MySQLManager get() {
        return instance == null ? instance = new MySQLManager() : instance;
    }

    //连接数据库
    private void connectMySQL()
    {
        try {
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite:"+ main.plugin.getConfig().getString("dbname")+".db");
        } catch (SQLException | ClassNotFoundException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
    }

    public void enableMySQL()
    {
        connectMySQL();
        String cmd1 = SQLCommand.CREATE_player.commandToString();
        String cmd2 = SQLCommand.CREATE_activeness.commandToString();
        String cmd3 = SQLCommand.CREATE_attendance.commandToString();
        String cmd4 = SQLCommand.CREATE_finishedtasks.commandToString();
        String cmd5 = SQLCommand.CREATE_unfinishedtasks.commandToString();
        String cmd6 = SQLCommand.CREATE_tasks.commandToString();
        String cmd7 = SQLCommand.CREATE_goods.commandToString();
        String cmd8 = SQLCommand.CREATE_sell.commandToString();
        String cmd9= SQLCommand.CREATE_dailytasks.commandToString();
        try {
            PreparedStatement ps1 = connection.prepareStatement(cmd1);
            PreparedStatement ps2 = connection.prepareStatement(cmd2);
            PreparedStatement ps3 = connection.prepareStatement(cmd3);
            PreparedStatement ps4 = connection.prepareStatement(cmd4);
            PreparedStatement ps5 = connection.prepareStatement(cmd5);
            PreparedStatement ps6 = connection.prepareStatement(cmd6);
            PreparedStatement ps7 = connection.prepareStatement(cmd7);
            PreparedStatement ps8 = connection.prepareStatement(cmd8);
            PreparedStatement ps9 = connection.prepareStatement(cmd8);
            ps1.executeUpdate();
            ps2.executeUpdate();
            ps3.executeUpdate();
            ps4.executeUpdate();
            ps5.executeUpdate();
            ps6.executeUpdate();
            ps7.executeUpdate();
            ps8.executeUpdate();
            ps9.executeUpdate();
            ps1.close();
            ps2.close();
            ps3.close();
            ps4.close();
            ps5.close();
            ps6.close();
            ps7.close();
            ps8.close();
            ps9.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }



    public void doCommand(PreparedStatement ps)
    {
        try {
            ps.executeUpdate();
        } catch (SQLException e) {
//            sender.sendMessage("执行指令失败，以下为错误提示");
            e.printStackTrace();
        }
    }

    //断开连接
    public void shutdown() {
        try {
            connection.close();
        } catch (SQLException e) {
            //断开连接失败
            e.printStackTrace();
        }
    }

    //插入玩家信息
    public void insertPlayer(String UUID,String PlayerName) {
        try {
            PreparedStatement ps;
            String s = SQLCommand.ADD_player.commandToString();
            ps = connection.prepareStatement(s);
            //ps.setInt(1, Integer.parseInt(data1));
            ps.setString(1, UUID);
            ps.setString(2, PlayerName);
            doCommand(ps);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (NumberFormatException e) {
//            sender.sendMessage("输入的不是整数，插入失败");
        }
    }

    //删除玩家信息
    public void deletePlayer(String UUID){
        try{
            PreparedStatement ps;
            String s = SQLCommand.DELETE_player.commandToString();
            ps = connection.prepareStatement(s);
            ps.setString(1,UUID);
            doCommand(ps);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //玩家表中是否有该玩家信息
    public boolean hasPlayer(String UUID){
        try{
            String s = SQLCommand.SELECT_player.commandToString();
            PreparedStatement ps = connection.prepareStatement(s);
            ps.setString(1,UUID);
            //查询
            ResultSet resultSet = ps.executeQuery();
            if(resultSet.getRow() != 0)
                return true;
            else
                return false;


        } catch (SQLException e) {
            e.printStackTrace();
            return false;
//            sender.sendMessage("输入有误");
        }
    }

//    //查找玩家信息
//    public void selectPlayer(String UUID,CommandSender sender){
//        try{
//            String s = SQLCommand.SELECT_player.commandToString();
//            PreparedStatement ps = connection.prepareStatement(s);
//            ps.setString(1,UUID);
//            //查询
//            ResultSet resultSet = ps.executeQuery();
//            while (resultSet.getRow() != 0){
//                String info = resultSet.getString("info");
//                sender.sendMessage(info);
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
////            sender.sendMessage("输入有误");
//        }
//    }

    //签到
    //签到表中是否有该玩家信息
    public boolean hasPlayerAttendance(String UUID){
        try{
            String s = SQLCommand.SELECT_attendance.commandToString();
            PreparedStatement ps = connection.prepareStatement(s);
            ps.setString(1,UUID);
            //查询
            ResultSet resultSet = ps.executeQuery();
            if(resultSet.getRow() != 0)
                return true;
            else
                return false;

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
//            sender.sendMessage("输入有误");
        }
    }

    //插入签到信息
    public void insertAttendance(String UUID,int totalTime, String monthRecord) {
        try {
            PreparedStatement ps;
            String s = SQLCommand.ADD_attendance.commandToString();
            ps = connection.prepareStatement(s);
            //ps.setInt(1, Integer.parseInt(data1));
            ps.setString(1, UUID);
            ps.setInt(2, totalTime);
            ps.setString(3,monthRecord);
            doCommand(ps);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (NumberFormatException e) {
//            sender.sendMessage("输入的不是整数，插入失败");
        }
    }

    //更新签到信息
    public void updateAttendance(String UUID,int totalTime,String monthRecord){
        try{
            //签到表中有该玩家信息
            if(hasPlayerAttendance(UUID))
            {
                PreparedStatement ps;
                String s = SQLCommand.UPDATE_attendance.commandToString();
                ps = connection.prepareStatement(s);
                ps.setInt(1, totalTime);
                ps.setString(2, monthRecord);
                ps.setString(3, UUID);
                doCommand(ps);
            }
            //签到表中没有该玩家信息
            else
            {
                insertAttendance(UUID,totalTime,monthRecord);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //查询月签到具体信息
    public String selectMonthRecord(String UUID){
        try{
            String s = SQLCommand.SELECT_attendance.commandToString();
            PreparedStatement ps = connection.prepareStatement(s);
            ps.setString(1,UUID);
            ResultSet resultSet = ps.executeQuery();
            //查询到了数据
            //需要判断返回值是否为error
            if(resultSet.getRow() != 0)
                return resultSet.getString(3);
            else
                return null;

        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    //查询月签到天数
    public int selectTotalTime(String UUID){
        try{
            String s = SQLCommand.SELECT_attendance.commandToString();
            PreparedStatement ps = connection.prepareStatement(s);
            ps.setString(1,UUID);
            ResultSet resultSet = ps.executeQuery();
            //查询到了数据
            //需要判断返回值是否为-1
            if(resultSet.getRow() != 0)
                return resultSet.getInt(2);
            else
                return -1;

        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }
    }


    //活跃度
    //活跃度表中是否有该玩家信息
    public boolean hasPlayerActiveness(String UUID){
        try{
            String s = SQLCommand.SELECT_activeness.commandToString();
            PreparedStatement ps = connection.prepareStatement(s);
            ps.setString(1,UUID);
            //查询
            ResultSet resultSet = ps.executeQuery();
            if(resultSet.getRow() != 0)
                return true;
            else
                return false;

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
//            sender.sendMessage("输入有误");
        }
    }

    //插入活跃度信息
    public void insertActiveness(String UUID,int totalTime, String monthRecord) {
        try {
            PreparedStatement ps;
            String s = SQLCommand.ADD_attendance.commandToString();
            ps = connection.prepareStatement(s);
            //ps.setInt(1, Integer.parseInt(data1));
            ps.setString(1, UUID);
            ps.setInt(2, totalTime);
            ps.setString(3,monthRecord);
            doCommand(ps);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (NumberFormatException e) {
//            sender.sendMessage("输入的不是整数，插入失败");
        }
    }

    //更新活跃度信息
    public void updateActiveness(String UUID,int totalTime,String monthRecord){
        try{
            //签到表中有该玩家信息
            if(hasPlayerAttendance(UUID))
            {
                PreparedStatement ps;
                String s = SQLCommand.UPDATE_attendance.commandToString();
                ps = connection.prepareStatement(s);
                ps.setInt(1, totalTime);
                ps.setString(2, monthRecord);
                ps.setString(3, UUID);
                doCommand(ps);
            }
            //签到表中没有该玩家信息
            else
            {
                insertAttendance(UUID,totalTime,monthRecord);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //查询月活跃度
    public int selectActivenessMonth(String UUID){
        try{
            String s = SQLCommand.SELECT_activeness.commandToString();
            PreparedStatement ps = connection.prepareStatement(s);
            ps.setString(1,UUID);
            ResultSet resultSet = ps.executeQuery();
            //查询到了数据
            //需要判断返回值是否为-1
            if(resultSet.getRow() != 0)
                return resultSet.getInt(2);
            else
                return -1;

        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }
    }

    //任务库
    public void insertTasks(int ID,String Name,String Type,String Description,String Request){
        try {
            String cmd = SQLCommand.ADD_tasks.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1, ID);
            preparedStatement.setString(2,Name);
            preparedStatement.setString(3,Type);
            preparedStatement.setString(4,Description);
            preparedStatement.setString(5,Request);
            doCommand(preparedStatement);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public void deleteTasks(int ID){
        try {
            String cmd = SQLCommand.DELETE_tasks.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1, ID);
            doCommand(preparedStatement);
        } catch (SQLException e) {
            e.printStackTrace();

        }
    }

    public List<Task> selectTasks(){
        List<Task> list = new ArrayList<>();
        try{

            String cmd = SQLCommand.SELECT_tasks.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                Gson g = new Gson();
                Task t = new Task();
                t.setID(resultSet.getInt(0));
                t.setTaskName(resultSet.getString(2));
                t.setTaskType(resultSet.getString(3));
                t.setTaskDescription(resultSet.getString(4));
                t.setTasks(g.fromJson(resultSet.getString(5), HashMap.class));
                list.add(t);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public String selectTasksName(int ID){
        try {
            String cmd = SQLCommand.SELECT_tasksName.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1,ID);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.getRow() != 0)
                return  resultSet.getString(2);
            else
                return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    public String selectTasksType(int ID){
        try {
            String cmd = SQLCommand.SELECT_tasksType.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1,ID);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.getRow() != 0)
                return  resultSet.getString(3);
            else
                return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    public String selectTasksDescription(int ID){
        try {
            String cmd = SQLCommand.SELECT_tasksDescription.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1,ID);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.getRow() != 0)
                return  resultSet.getString(4);
            else
                return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    public String selectTasksRequest(int ID){
        try {
            String cmd = SQLCommand.SELECT_tasksRequest.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1,ID);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.getRow() != 0)
                return  resultSet.getString(5);
            else
                return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    //任务存档
    public void insertUnfinishedtask(Date Date,String Data){
        try {
            String cmd = SQLCommand.ADD_unfinishedtasks.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setDate(1, Date);
            preparedStatement.setString(2,Data);
            doCommand(preparedStatement);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public void  deleteUnfinishedtask(Date Date){
        try {
            String cmd = SQLCommand.DELETE_unfinishedtasks.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setDate(1, Date);
            doCommand(preparedStatement);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public String selectUnfinishedtask(Date Date){
        try {
            String cmd = SQLCommand.SELECT_unfinishedtasks.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setDate(1,Date);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.getRow() != 0)
                return  resultSet.getString(2);
            else
                return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public void updateUnfinished(Date date,String data){
        try{
            String cmd = SQLCommand.UPDATE_unfinished.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setString(1,data);
            preparedStatement.setDate(2,date);
            preparedStatement.execute();
            preparedStatement.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public boolean hasUnfinished(Date date){
       try{
           String cmd = SQLCommand.SELECT_unfinishedtasks.commandToString();
           PreparedStatement preparedStatement = connection.prepareStatement(cmd);
           preparedStatement.setDate(1,date);
           ResultSet resultSet = preparedStatement.executeQuery();
           if(resultSet.getRow() != 0)
               return  true;
           else
               return false;
       }catch(SQLException e){
           e.printStackTrace();
       }
      return false;
    }
    //任务记录
    public void insertFinishedtask(Date Date,String Data){
        try {
            String cmd = SQLCommand.ADD_finishedtasks.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setDate(1, Date);
            preparedStatement.setString(2,Data);
            doCommand(preparedStatement);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public void  deleteFinishedtask(Date Date){
        try {
            String cmd = SQLCommand.DELETE_finishedtasks.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setDate(1, Date);
            doCommand(preparedStatement);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public String selectFinishedtask(Date Date){
        try {
            String cmd = SQLCommand.SELECT_finishedtasks.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setDate(1,Date);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.getRow() != 0)
                return  resultSet.getString(2);
            else
                return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    public void updateFinished(Date date,String data){
        try{
            String cmd = SQLCommand.UPDATE_finished.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setString(1,data);
            preparedStatement.setDate(2,date);
            preparedStatement.execute();
            preparedStatement.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public boolean hasFinished(Date date){
        try{
            String cmd = SQLCommand.SELECT_finishedtasks.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setDate(1,date);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.getRow() != 0)
                return  true;
            else
                return false;
        }catch(SQLException e){
            e.printStackTrace();
        }
        return false;
    }
    //每日任务
    public void insertDailytask(Date Date,String Data){
        try {
            String cmd = SQLCommand.ADD_dailytasks.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setDate(1, Date);
            preparedStatement.setString(2,Data);
            doCommand(preparedStatement);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public void  deleteDailytask(Date Date){
        try {
            String cmd = SQLCommand.DELETE_dailytasks.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setDate(1, Date);
            doCommand(preparedStatement);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public String selectDailytask(Date Date){
        try {
            String cmd = SQLCommand.SELECT_dailytasks.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setDate(1,Date);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.getRow()!=0)
                return  resultSet.getString(2);
            else
                return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    public void updateDaily(Date date,String data){
        try{
            String cmd = SQLCommand.UPDATE_daily.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setString(1,data);
            preparedStatement.setDate(2,date);
            preparedStatement.execute();
            preparedStatement.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public boolean hasDaily(Date date){
        try{
            String cmd = SQLCommand.SELECT_dailytasks.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setDate(1,date);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.getRow() != 0)
                return  true;
            else
                return false;
        }catch(SQLException e){
            e.printStackTrace();
        }
        return false;
    }
    //商店商品
    public void insertGoods(int ID,String Name,int Number,int Price,String Type) {
        try {
            String cmd = SQLCommand.ADD_goods.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1, ID);
            preparedStatement.setString(2,Name);
            preparedStatement.setInt(3,Number);
            preparedStatement.setInt(4,Price);
            preparedStatement.setString(5,Type);
            doCommand(preparedStatement);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public void deleteGoods(int ID){
        try {
            String cmd = SQLCommand.DELETE_goods.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1, ID);
            doCommand(preparedStatement);
        } catch (SQLException e) {
            e.printStackTrace();

        }
    }
    public String selectGoodsName(int ID){
        try {
            String cmd = SQLCommand.SELECT_goodsname.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1,ID);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.getRow() != 0)
                return  resultSet.getString(2);
            else
                return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    public int selectGoodsNumber(int ID){
        try {
            String cmd = SQLCommand.SELECT_goodsNumber.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1,ID);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.getRow() != 0)
                return  resultSet.getInt(3);
            else
                return -1;
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }
    }
    public int selectGoodsPrice(int ID){
        try {
            String cmd = SQLCommand.SELECT_goodsprice.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1,ID);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.getRow() != 0)
                return  resultSet.getInt(4);
            else
                return -1;
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }
    }
    public String selectGoodsType(int ID){
        try {
            String cmd = SQLCommand.SELECT_goodstype.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1,ID);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.getRow() != 0)
                return  resultSet.getString(5);
            else
                return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    //商店记录
    public void insertSell(int ID,String UUID,String Name,int Number,int Price,Timestamp Time) {
        try {

            String cmd = SQLCommand.ADD_sell.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1, ID);
            preparedStatement.setString(2,UUID);
            preparedStatement.setString(3,Name);
            preparedStatement.setInt(4,Number);
            preparedStatement.setInt(5,Price);
            preparedStatement.setTimestamp(6,Time);
            doCommand(preparedStatement);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public  void deleteSell(int ID){
        try {
            String cmd = SQLCommand.DELETE_sell.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1, ID);
            doCommand(preparedStatement);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public String selectSellUUID(int ID){
        try {
            String cmd = SQLCommand.SELECT_sellUUID.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1,ID);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.getRow() != 0)
                return  resultSet.getString(2);
            else
                return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    public String selectSellName(int ID){
        try {
            String cmd = SQLCommand.SELECT_sellName.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1,ID);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.getRow() != 0)
                return  resultSet.getString(3);
            else
                return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    public int selectSellNumber(int ID){
        try {
            String cmd = SQLCommand.SELECT_sellNumber.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1,ID);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.getRow() != 0)
                return  resultSet.getInt(4);
            else
                return -1;
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }
    }
    public int selectSellPrice(int ID){
        try {
            String cmd = SQLCommand.SELECT_sellPrice.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1,ID);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.getRow() != 0)
                return  resultSet.getInt(5);
            else
                return -1;
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }
    }
    public Timestamp selectSellTime(int ID){
        try {

            String cmd = SQLCommand.SELECT_sellTime.commandToString();
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1,ID);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.getRow() != 0)
                return  resultSet.getTimestamp(6);
            else
                return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

}
